SRAllocationNotice

V8 Message Definiton

METADATA

Attribute	Value
Topic	3695-order-allocation
MLink Token	ClientTrading
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
allocNumber	BIGINT	PRI	0	unique number assigned to this allocation notice
tradeDate	DATE		'1900-01-01'
clientFirm	VARCHAR(16)		''
omniAccnt	VARCHAR(16)		''	omnibus accnt SR accnt
secKey_at	enum - AssetType		'None'	security key stock future or option
secKey_ts	enum - TickerSrc		'None'	security key stock future or option
secKey_tk	VARCHAR(12)		''	security key stock future or option
secKey_yr	SMALLINT UNSIGNED		0	security key stock future or option
secKey_mn	TINYINT UNSIGNED		0	security key stock future or option
secKey_dy	TINYINT UNSIGNED		0	security key stock future or option
secKey_xx	DOUBLE		0	security key stock future or option
secKey_cp	enum - CallPut		'Call'	security key stock future or option
secType	enum - SpdrKeyType		'None'	security type
mlegLegKey	TINYTEXT		''	secKeyStringKeylegRatiosidepositionEffect secKeyStringKeylegRatiosidepositionEffect
orderSide	enum - BuySell		'None'	order side always buy for MLEG orders
ssaleFlag	enum - ShortSaleFlag		'None'	ssale flag None if not stock
positionType	enum - PositionType		'None'	positionType None if not equity option
noticeState	enum - NoticeState		'None'	indicates that this noticed is ready to forward to a custodian
noticeText	TINYTEXT		''
allocMethod	enum - AllocMethod		'None'	method for delivery of the allocation instruction to the broker
execBrkrCode	VARCHAR(16)		''	used for FIX routing can be null
allocExDest	VARCHAR(16)		''	used for FIX routing
orderShape	enum - SpdrOrderShape		'None'	Single or MLeg
cumFillQuantity	INT		0	total fill size of all ChildOrders
avgFillPrice	DOUBLE		0	parent order average fill price
netMoney	DOUBLE		0	net allocation money
modifiedBy	VARCHAR(24)		''	user who last modified this record
modifiedIn	enum - SysEnvironment		'None'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	timestamp of last modification
ChildOrdersList	JSON		'JSON_ARRAY()'
FragmentsList	JSON		'JSON_ARRAY()'
LegPosTypeList	JSON		'JSON_ARRAY()'
OrderLegsList	JSON		'JSON_ARRAY()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
allocNumber	1

JSON Block (ChildOrdersList)

Field	Type	Comment
clOrdId	enum - clOrdId
orderSize	enum - orderSize
cumFillQuantity	enum - cumFillQuantity
avgFillPrice	enum - avgFillPrice
streetClOrdId	enum - streetClOrdId	usually clOrdIDX16 but can be different if street gateway was translating clOrdId
streetOrderId	enum - streetOrderId	street side order ID for canceling order

JSON Block (FragmentsList)

Field	Type	Comment
accnt	enum - accnt	SR alloc accnt
clientFirm	enum - clientFirm	SR client firm
allocAccnt	enum - allocAccnt	clearing firm alloc accnt maps to FixAllocAccount
allocFragId	enum - allocFragId	unique ID of this allocfragment maps to FixIndividualAllocID
allocSize	enum - allocSize	accnt alloc size

JSON Block (LegPosTypeList)

Field	Type	Comment
secKey	enum - secKey
secType	enum - SpdrKeyType
accnt	enum - accnt	SR alloc accnt matches fragment
clientFirm	enum - clientFirm	SR client firm
ssaleFlag	enum - ShortSaleFlag	ssale flag if secType Stock
positionType	enum - PositionType	positionType if secType Stock
firmPosition	enum - firmPosition

JSON Block (OrderLegsList)

Field	Type	Comment
secKey	enum - secKey
secType	enum - SpdrKeyType
mult	enum - mult
side	enum - BuySell
legId	enum - legId	SR leg Id
altLegId	enum - altLegId	client leg Id usually from a FIX order
sumLegQty	enum - sumLegQty
sumLegMny	enum - sumLegMny

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgSRAllocationNotice` (
    `allocNumber` BIGINT NOT NULL DEFAULT 0 COMMENT 'unique number assigned to this allocation notice',
    `tradeDate` DATE NOT NULL DEFAULT '1900-01-01',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '',
    `omniAccnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'omnibus accnt (SR accnt)',
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'security key (stock, future, or option)',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'security key (stock, future, or option)',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'security key (stock, future, or option)',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'security key (stock, future, or option)',
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'security key (stock, future, or option)',
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'security key (stock, future, or option)',
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0 COMMENT 'security key (stock, future, or option)',
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call' COMMENT 'security key (stock, future, or option)',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'security type',
    `mlegLegKey` TINYTEXT NOT NULL DEFAULT '' COMMENT '[secKey.StringKey;legRatio;side;positionEffect, secKey.StringKey;legRatio;side;positionEffect, ...]',
    `orderSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None' COMMENT 'order side (always buy for MLEG orders)',
    `ssaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'ssale flag (None if not stock)',
    `positionType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None' COMMENT 'positionType (None if not equity option)',
    `noticeState` ENUM('None','ReadyToSend','HoldForReview','Canceled','Error') NOT NULL DEFAULT 'None' COMMENT 'indicates that this noticed is ready to forward to a custodian',
    `noticeText` TINYTEXT NOT NULL DEFAULT '',
    `allocMethod` ENUM('None','FIX','FilePAS') NOT NULL DEFAULT 'None' COMMENT 'method for delivery of the allocation instruction to the broker',
    `execBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'used for FIX routing (can be null)',
    `allocExDest` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'used for FIX routing',
    `orderShape` ENUM('None','Single','Cross','MLeg','MLegCross') NOT NULL DEFAULT 'None' COMMENT 'Single or MLeg',
    `cumFillQuantity` INT NOT NULL DEFAULT 0 COMMENT 'total fill size of all ChildOrders',
    `avgFillPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'parent order average fill price',
    `netMoney` DOUBLE NOT NULL DEFAULT 0 COMMENT 'net allocation money',
    `modifiedBy` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'user who last modified this record',
    `modifiedIn` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'timestamp of last modification',
    `ChildOrdersList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(ChildOrdersList)),
    `FragmentsList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(FragmentsList)),
    `LegPosTypeList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(LegPosTypeList)),
    `OrderLegsList` JSON NOT NULL DEFAULT JSON_ARRAY() CHECK(JSON_VALID(OrderLegsList)),
    PRIMARY KEY USING HASH (`allocNumber`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `allocNumber`,
    `tradeDate`,
    `clientFirm`,
    `omniAccnt`,
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `mlegLegKey`,
    `orderSide`,
    `ssaleFlag`,
    `positionType`,
    `noticeState`,
    `noticeText`,
    `allocMethod`,
    `execBrkrCode`,
    `allocExDest`,
    `orderShape`,
    `cumFillQuantity`,
    `avgFillPrice`,
    `netMoney`,
    `timestamp`,
    `ChildOrdersList`,
    `FragmentsList`,
    `LegPosTypeList`,
    `OrderLegsList`
FROM `SRTrade`.`MsgSRAllocationNotice`
WHERE 
    /* Replace with a BIGINT */ 
    `allocNumber` = 1234567890;

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='SRAllocationNotice' ORDER BY ordinal_position ASC;